﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;
using System.Configuration;
using FindPackage.DTO;

namespace FindPackage.DAL
{
    class DBConnection : IDBConnection
    {
        #region Variaveis
        private string strConnection = ConfigurationManager.AppSettings["CONN_MYSQL_STRING"].ToString();
        private MySqlCommand cmdMysql;
        private MySqlConnection connMysql;
        private Retorno_DTO oRetorno_DTO;
        #endregion

        #region Metodos
        public Retorno_DTO Executar(string strComando, MySqlParameter[] param, System.Data.CommandType cmdTipo)
        {
            try
            {
                oRetorno_DTO = new Retorno_DTO();
                connMysql = new MySqlConnection(strConnection);
                connMysql.Open();
                cmdMysql = new MySqlCommand(strComando);
                cmdMysql.Connection = connMysql;
                cmdMysql.CommandType = cmdTipo;
                cmdMysql.Parameters.Clear();

                if (param != null)
                    foreach (MySqlParameter p in param)
                    {
                        cmdMysql.Parameters.Add(p);
                    }

                using (MySqlTransaction trans = connMysql.BeginTransaction())
                {
                    cmdMysql.Transaction = trans;
                    try
                    {
                        cmdMysql.ExecuteNonQuery();
                        oRetorno_DTO.pCOD_RETORNO = Convert.ToInt32(cmdMysql.Parameters["?COD_RETORNO"].Value);
                        oRetorno_DTO.pDSC_RETORNO = cmdMysql.Parameters["?DSC_RETORNO"].Value.ToString();

                        if (cmdMysql.Parameters["?XML_RETORNO"].Value != null)
                            oRetorno_DTO.pXML_RETORNO = cmdMysql.Parameters["?XML_RETORNO"].Value.ToString();
                        else
                            oRetorno_DTO.pXML_RETORNO = "";

                        trans.Commit();
                        return oRetorno_DTO;

                    }
                    catch (Exception ex)
                    {
                        trans.Rollback();
                        throw ex;
                    }

                }

            }
            catch (Exception ex)
            {

                throw ex;
            }

        }
        public Retorno_DTO ExecutarReader(string strComando, MySqlParameter[] param, System.Data.CommandType cmdTipo)
        {
            try
            {
                oRetorno_DTO = new Retorno_DTO();
                connMysql = new MySqlConnection(strConnection);
                connMysql.Open();
                cmdMysql = new MySqlCommand(strComando);
                cmdMysql.Connection = connMysql;
                cmdMysql.CommandType = cmdTipo;
                cmdMysql.Parameters.Clear();

                if (param != null)
                    foreach (MySqlParameter p in param)
                    {
                        cmdMysql.Parameters.Add(p);
                    }

                oRetorno_DTO.pMySqlDataReader = cmdMysql.ExecuteReader();
                oRetorno_DTO.pCOD_RETORNO = Convert.ToInt32(cmdMysql.Parameters["?COD_RETORNO"].Value);
                oRetorno_DTO.pDSC_RETORNO = cmdMysql.Parameters["?DSC_RETORNO"].Value.ToString();

                return oRetorno_DTO;

            }
            catch (Exception ex)
            {

                throw ex;
            }

        }
        #endregion
    }
}
